set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go






--CREATEd:02/08/2010
--Author: nnamthach@gmail.com
ALTER procedure [dbo].[procCONGNO_getAllCongNoPSTHU](
@DateF as Datetime,
@DateT as Datetime
)
AS
BEGIN 
SELECT     SUM(CONGNO.SOTIEN) AS SOTIEN, CONGNO.MAKH, CUSTINFO.TENGD, CUSTINFO.DIACHI
FROM         CONGNO INNER JOIN
                      CUSTINFO ON CONGNO.MAKH = CUSTINFO.CUSTID
WHERE     (CONGNO.NGAYCT < @DateF) AND (CONGNO.SOCTTT = 'BH')
GROUP BY CONGNO.MAKH, CUSTINFO.TENGD, CUSTINFO.DIACHI

SELECT     SUM(CONGNO.SOTIEN) AS SOTIEN, CONGNO.MAKH, CONGNO.D_C, CUSTINFO.TENGD, CUSTINFO.DIACHI
FROM         CONGNO INNER JOIN
                      CUSTINFO ON CONGNO.MAKH = CUSTINFO.CUSTID
WHERE     (CONGNO.NGAYCT BETWEEN @DateF AND @DateT) AND (CONGNO.PHAITHU = 'True') AND (CONGNO.SOCTTT = 'BH')
GROUP BY CONGNO.MAKH, CUSTINFO.TENGD, CUSTINFO.DIACHI, CONGNO.D_C

SELECT       SUM(CONGNO.SOTIEN) AS SOTIEN, CONGNO.MAKH, CUSTINFO.TENGD, CUSTINFO.DIACHI
FROM         CONGNO INNER JOIN
                      CUSTINFO ON CONGNO.MAKH = CUSTINFO.CUSTID
WHERE     (CONGNO.NGAYCT BETWEEN @DateF AND @DateT) AND (CONGNO.PHAITHU = 'False') and (SOCTTT='BH' )
GROUP BY  CONGNO.MAKH, CUSTINFO.TENGD, CUSTINFO.DIACHI

SELECT     SUM(CONGNO.SOTIEN) AS SOTIEN, CONGNO.MAKH, CUSTINFO.TENGD, CUSTINFO.DIACHI
FROM         CONGNO INNER JOIN
                      CUSTINFO ON CONGNO.MAKH = CUSTINFO.CUSTID
WHERE     (CONGNO.NGAYCT <= @DateT)  AND (CONGNO.SOCTTT = 'BH')
GROUP BY CONGNO.MAKH, CUSTINFO.TENGD, CUSTINFO.DIACHI

SELECT     (SELECT     SUM(SOTIEN) AS SOTIEN
                       FROM          CONGNO
                       WHERE      (NGAYCT BETWEEN @DateF AND @DateT) AND (PHAITHU = 'True') AND (SOCTTT = 'BH')) AS SOTIENPST,
                          (SELECT     SUM(SOTIEN) AS SOTIEN
                            FROM          CONGNO AS CONGNO_2
                            WHERE      (NGAYCT BETWEEN @DateF AND @DateT) AND (PHAITHU = 'False') AND (SOCTTT = 'BH')) AS SOTIENPSG, CONGNO_1.MAKH, CONGNO_1.D_C, 
                      CUSTINFO_1.TENGD, CUSTINFO_1.DIACHI
FROM         CONGNO AS CONGNO_1 INNER JOIN
                      CUSTINFO AS CUSTINFO_1 ON CONGNO_1.MAKH = CUSTINFO_1.CUSTID
WHERE     (CONGNO_1.NGAYCT BETWEEN @DateF AND @DateT) AND (CONGNO_1.PHAITHU = 'True') AND (CONGNO_1.SOCTTT = 'BH')
GROUP BY CONGNO_1.MAKH, CUSTINFO_1.TENGD, CUSTINFO_1.DIACHI, CONGNO_1.D_C
HAVING      ((SELECT     SUM(SOTIEN) AS SOTIEN
                         FROM         CONGNO AS CONGNO_2
                         WHERE     (NGAYCT BETWEEN @DateF AND @DateT) AND (PHAITHU = 'False') AND (SOCTTT = 'BH')) = 0) AND
                          ((SELECT     SUM(SOTIEN) AS SOTIEN
                              FROM         CONGNO AS CONGNO_3
                              WHERE     (NGAYCT BETWEEN @DateF AND @DateT) AND (PHAITHU = 'True') AND (SOCTTT = 'BH')) = 0)

SELECT     (SELECT     SUM(SOTIEN) AS SOTIEN
                       FROM          CONGNO
                       WHERE      (NGAYCT BETWEEN @DateF AND @DateT) AND (PHAITHU = 'True') AND (SOCTTT = 'BH')) AS SOTIENPST,
                          (SELECT     SUM(SOTIEN) AS SOTIEN
                            FROM          CONGNO AS CONGNO_2
                            WHERE      (NGAYCT BETWEEN @DateF AND @DateT) AND (PHAITHU = 'False') AND (SOCTTT = 'BH')) AS SOTIENPSG, CONGNO_1.MAKH, CONGNO_1.D_C, 
                      CUSTINFO_1.TENGD, CUSTINFO_1.DIACHI
FROM         CONGNO AS CONGNO_1 INNER JOIN
                      CUSTINFO AS CUSTINFO_1 ON CONGNO_1.MAKH = CUSTINFO_1.CUSTID
WHERE     (CONGNO_1.NGAYCT BETWEEN @DateF AND @DateT) AND (CONGNO_1.PHAITHU = 'True') AND (CONGNO_1.SOCTTT = 'BH')
GROUP BY CONGNO_1.MAKH, CUSTINFO_1.TENGD, CUSTINFO_1.DIACHI, CONGNO_1.D_C
HAVING      ((SELECT     SUM(SOTIEN) AS SOTIEN
                         FROM         CONGNO AS CONGNO_2
                         WHERE     (NGAYCT BETWEEN @DateF AND @DateT) AND (PHAITHU = 'False') AND (SOCTTT = 'BH')) = 0) AND
                          ((SELECT     SUM(SOTIEN) AS SOTIEN
                              FROM         CONGNO AS CONGNO_3
                              WHERE     (NGAYCT BETWEEN @DateF AND @DateT) AND (PHAITHU = 'True') AND (SOCTTT = 'BH')) > 0)


SELECT     (SELECT     SUM(SOTIEN) AS SOTIEN
                       FROM          CONGNO
                       WHERE      (NGAYCT BETWEEN @DateF AND @DateT) AND (PHAITHU = 'True') AND (SOCTTT = 'BH')) AS SOTIENPST,
                          (SELECT     SUM(SOTIEN) AS SOTIEN
                            FROM          CONGNO AS CONGNO_2
                            WHERE      (NGAYCT BETWEEN @DateF AND @DateT) AND (PHAITHU = 'False') AND (SOCTTT = 'BH')) AS SOTIENPSG, CONGNO_1.MAKH, CONGNO_1.D_C, 
                      CUSTINFO_1.TENGD, CUSTINFO_1.DIACHI
FROM         CONGNO AS CONGNO_1 INNER JOIN
                      CUSTINFO AS CUSTINFO_1 ON CONGNO_1.MAKH = CUSTINFO_1.CUSTID
WHERE     (CONGNO_1.NGAYCT BETWEEN @DateF AND @DateT) AND (CONGNO_1.PHAITHU = 'True') AND (CONGNO_1.SOCTTT = 'BH')
GROUP BY CONGNO_1.MAKH, CUSTINFO_1.TENGD, CUSTINFO_1.DIACHI, CONGNO_1.D_C
HAVING      ((SELECT     SUM(SOTIEN) AS SOTIEN
                         FROM         CONGNO AS CONGNO_2
                         WHERE     (NGAYCT BETWEEN @DateF AND @DateT) AND (PHAITHU = 'False') AND (SOCTTT = 'BH')) < 0) AND
                          ((SELECT     SUM(SOTIEN) AS SOTIEN
                              FROM         CONGNO AS CONGNO_3
                              WHERE     (NGAYCT BETWEEN @DateF AND @DateT) AND (PHAITHU = 'True') AND (SOCTTT = 'BH')) = 0)

SELECT     (SELECT     SUM(SOTIEN) AS SOTIEN
                       FROM          CONGNO
                       WHERE      (NGAYCT BETWEEN @DateF AND @DateT) AND (PHAITHU = 'True') AND (SOCTTT = 'BH')) AS SOTIENPST,
                          (SELECT     SUM(SOTIEN) AS SOTIEN
                            FROM          CONGNO AS CONGNO_2
                            WHERE      (NGAYCT BETWEEN @DateF AND @DateT) AND (PHAITHU = 'False') AND (SOCTTT = 'BH')) AS SOTIENPSG, CONGNO_1.MAKH, CONGNO_1.D_C, 
                      CUSTINFO_1.TENGD, CUSTINFO_1.DIACHI
FROM         CONGNO AS CONGNO_1 INNER JOIN
                      CUSTINFO AS CUSTINFO_1 ON CONGNO_1.MAKH = CUSTINFO_1.CUSTID
WHERE     (CONGNO_1.NGAYCT BETWEEN @DateF AND @DateT) AND (CONGNO_1.PHAITHU = 'True') AND (CONGNO_1.SOCTTT = 'BH')
GROUP BY CONGNO_1.MAKH, CUSTINFO_1.TENGD, CUSTINFO_1.DIACHI, CONGNO_1.D_C
HAVING      ((SELECT     SUM(SOTIEN) AS SOTIEN
                         FROM         CONGNO AS CONGNO_2
                         WHERE     (NGAYCT BETWEEN @DateF AND @DateT) AND (PHAITHU = 'False') AND (SOCTTT = 'BH')) < 0) AND
                          ((SELECT     SUM(SOTIEN) AS SOTIEN
                              FROM         CONGNO AS CONGNO_3
                              WHERE     (NGAYCT BETWEEN @DateF AND @DateT) AND (PHAITHU = 'True') AND (SOCTTT = 'BH')) > 0)
END






